﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Publicuse.DBCore;
using Publicuse.Entity;
using Dapper;
using MySql.Data.MySqlClient;
using Publicuse.Util;
using Publicuse.Entity.AttributeUtil;
using System.Linq;
using static Dapper.SqlMapper;
using ServiceStack;
using Dapper.Contrib.Extensions;
using Microsoft.VisualBasic;
using Z.Dapper.Plus;
using StackExchange.Profiling;
using System.Data.Common;

namespace Publicuse.DBCore.MySql
{
    /// <summary>
    /// MySqlDapper数据库操作类
    /// </summary>
    public class MySqlDapperInstance
    {
        private string connstr;

        private static object locker = new object();

        private static MySqlDapperInstance mySqlInstance = null;

        static MySqlDapperInstance()
        {
            mySqlInstance = new MySqlDapperInstance();
        }

        public static MySqlDapperInstance GetInstance
        {
            get { return mySqlInstance; }
        }

        /// <summary>
        /// 数据库实例字段初始化
        /// </summary>
        /// <param name="constr"></param>
        public void Init(string constr)
        {
            if (string.IsNullOrEmpty(constr))
                return;

            this.connstr = constr;
        }

        /// <summary>
        /// 设置连接数据库字符串
        /// </summary>
        /// <param name="writeAndRead"></param>
        /// <returns></returns>
        private string SetConnecStr(WriteAndReadEnum writeAndRead)
        {
            string constr = string.Empty;
            switch (writeAndRead)
            {
                case WriteAndReadEnum.Write:
                    {
                        constr = this.connstr.Split('|')[0];
                    }
                    break;
                case WriteAndReadEnum.Read:
                    {
                        var newstrs = this.connstr.Split('|')[1].Split(',');
                        int index = new Random().Next(0, newstrs.Length - 1);
                        constr = newstrs[index];
                    }
                    break;
            }
            return constr;
        }

        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="msg"></param>
        /// <param name="writeAndRead"></param>
        /// <param name="action"></param>
        /// <param name="istran"></param>
        /// <returns></returns>
        private T InitConnction<T>(out string msg, WriteAndReadEnum writeAndRead, Func<IDbConnection, T> action, bool istran)
        {
            msg = string.Empty;
            T result = default(T);
            MiniProfiler profiler = MiniProfiler.StartNew("StartNew");
            var constr = SetConnecStr(writeAndRead);
            IDbTransaction dbTransaction = null;
            using (profiler.Step("Level1"))
            {
                DbConnection connection = new MySqlConnection(constr);
                using (IDbConnection con = new MySqlConnection(constr))
                {
                    if (MiniProfiler.Current != null)
                    {
                        connection = new StackExchange.Profiling.Data.ProfiledDbConnection(connection, MiniProfiler.Current);
                    }
                    try
                    {
                        connection.Open();
                        con.Open();
                        if (istran)
                        {
                            dbTransaction = con.BeginTransaction();
                            result = action.Invoke(con);
                            dbTransaction.Commit();
                        }
                        else
                        {
                            result = action.Invoke(con);
                        }
                        return result;
                    }
                    catch (Exception ex)
                    {
                        if (dbTransaction != null)
                        {
                            dbTransaction.Rollback();
                        }
                        msg = ex.Message;
                        if (!ex.Message.Contains("Unable to connect to any of the specified MySQL hosts"))
                        {
                            //LogUtil.GetInstance.WriteLogOld("连接MySql失败", ex.Message + "|" + constr, true);
                        }
                        connection.Close();
                        con.Close();
                        connection.Dispose();
                        con.Dispose();
                        return default(T);
                    }
                }
            }
            WriteLog(profiler);
            return result;
        }

        /// <summary>
        /// 新增信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public long Insert<T>(T entity, out string msg) where T : class, new()
        {
            long result = -1;
            msg = string.Empty;
            var bkd = this.InitConnction(out msg, WriteAndReadEnum.Write, (con) =>
            {
                result = con.Insert<T>(entity);
                return result;
            }, true);
            return result;
        }

        /// <summary>
        /// 批量新增信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ts"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public IEnumerable<T> Insert<T>(List<T> ts, out string msg)
        {
            var result = this.InitConnction(out msg, WriteAndReadEnum.Write, (con) =>
            {
                DapperPlusActionSet<T> result = con.BulkInsert<T>(ts);
                return result.Current;
            }, true);
            return result;
        }

        /// <summary>
        /// 修改信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Update<T>(T entity, out string msg) where T : class, new()
        {
            bool result = true;
            msg = string.Empty;
            result = this.InitConnction(out msg, WriteAndReadEnum.Write, (con) =>
            {
                result = con.Update<T>(entity, null, 120);
                return result;
            }, true);
            return result;
        }

        /// <summary>
        /// 批量修改信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ts"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public T Update<T>(IEnumerable<T> ts, out string msg) where T : class, new()
        {
            msg = string.Empty;
            var result = this.InitConnction(out msg, WriteAndReadEnum.Write, (con) =>
            {
                DapperPlusActionSet<T> result = con.BulkUpdate<T>(ts);
                return result.CurrentItem;
            }, true);
            return result;
        }

        /// <summary>
        /// 批量删除信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Delete<T>(List<string> ids, out string msg)
        {
            bool result = true;
            msg = string.Empty;
            this.InitConnction(out msg, WriteAndReadEnum.Write, (con) =>
            {
                var table = TableAtributeUtil.GetTable<T>();
                var dids = String.Join(",", ids);
                var sql = $"select * from {table} where Id in({dids})";
                var data = con.Query<T>(sql); ;
                con.BulkDelete(data);
                return true;
            }, true);
            return result;
        }

        /// <summary>
        /// 根据主键删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Delete<T>(long id, out string msg) where T : class, new()
        {
            var result = true;
            msg = string.Empty;
            result = this.InitConnction(out msg, WriteAndReadEnum.Write, (con) =>
            {
                var entity = con.Get<T>(id, null, 120);
                result = con.Delete<T>(entity, null, 120);
                return result;
            }, true);
            return result;
        }

        /// <summary>
        /// 根据SQL获取第一条信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public T QueryFirst<T>(string sql, out string msg) where T : class, new()
        {
            msg = string.Empty;
            T entity = default(T);
            this.InitConnction(out msg, WriteAndReadEnum.Read, (con) =>
            {
                entity = con.QueryFirst<T>(sql, null, null, 120);
                return entity;
            }, false);
            return entity;
        }

        /// <summary>
        /// 根据SQL条件查询信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> Query<T>(string sql, object param, out string msg)
        {
            msg = string.Empty;
            return this.InitConnction(out msg, WriteAndReadEnum.Read, (con) =>
            {
                return con.Query<T>(sql, param, null, true, 120).ToList();
            }, false);
        }

        /// <summary>
        /// 根据条件执行SQL 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool ExecuteSql(string sql, object param, out string msg)
        {
            msg = string.Empty;
            return this.InitConnction(out msg, WriteAndReadEnum.Write, (con) =>
            {
                var ipass = con.Execute(sql, param, null, 120) > -1;
                return ipass;
            }, true);
        }

        /// <summary>
        /// 根据SQL获取信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <param name="list"></param>
        public List<T> Query<T>(string sql, out string msg) where T : class, new()
        {
            msg = string.Empty;
            var list = new List<T>();
            list = this.InitConnction(out msg, WriteAndReadEnum.Read, (con) =>
            {
                var list = con.Query<T>(sql, null, null, true, 120).ToList();
                return list;
            }, false);
            return list;
        }

        public List<T> GetAll<T>(out string msg) where T : class, new()
        {
            msg = string.Empty;
            var list = new List<T>();
            list = this.InitConnction(out msg, WriteAndReadEnum.Read, (con) =>
            {
                var list = con.GetAll<T>(null, 120).ToList();
                return list;
            }, false);
            return list;
        }

        /// <summary>
        /// 通用CUD操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="cUDType"></param>
        /// <param name="exception"></param>
        /// <param name="sql"></param>
        /// <returns>返回操作结果</returns>
        public int CUDEntity<T>(T entity, CUDType cUDType, out string exception, string sql = null) where T : new()
        {
            exception = null;
            var sqlParameter = new DynamicParameters();
            CURDModel cURDModel = null;
            if (string.IsNullOrEmpty(sql))
            {
                switch (cUDType)
                {
                    case CUDType.Add:
                        {
                            sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.MySqlDapperInser, entity, out exception, out sql).GetDynamicParameters;
                        }
                        break;
                    case CUDType.Update:
                        {
                            sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.MySqlDapperUpdate, entity, out exception, out sql).GetDynamicParameters;
                        }
                        break;
                    case CUDType.Delete:
                        {
                            sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.MySqlDapperDelete, entity, out exception, out sql).GetDynamicParameters;
                        }
                        break;
                }
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Params, cUDType = cUDType };
            }
            else
            {
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Default, cUDType = cUDType };
            }
            return Execute(sql, sqlParameter, cURDModel, false, out exception);
        }

        /// <summary>
        /// 批量新增修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="cUDType"></param>
        /// <param name="exception"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<int> CUDEntity<T>(List<T> entitys, CUDType cUDType, out string exception, string sql = null) where T : new()
        {
            exception = null;
            var sqlParameter = new DynamicParameters();
            CURDModel cURDModel = null;
            List<DynamicParameters> listsqlParameter = new List<DynamicParameters>();
            List<string> listsql = new List<string>();
            if (string.IsNullOrEmpty(sql))
            {
                foreach (var entity in entitys)
                {
                    switch (cUDType)
                    {
                        case CUDType.Add:
                            {
                                sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.MySqlDapperInser, entity, out exception, out sql).GetDynamicParameters;
                            }
                            break;
                        case CUDType.Update:
                            {
                                sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.MySqlDapperUpdate, entity, out exception, out sql).GetDynamicParameters;
                            }
                            break;
                        case CUDType.Delete:
                            {
                                sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.MySqlDapperDelete, entity, out exception, out sql).GetDynamicParameters;
                            }
                            break;
                    }
                    listsqlParameter.Add(sqlParameter);
                    listsql.Add(sql);
                }
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Params, cUDType = cUDType };
            }
            else
            {
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Default, cUDType = cUDType };
            }
            return Execute(listsql, listsqlParameter, cURDModel, false, out exception);
        }

        /// <summary>
        /// 相当于把对数据库的命令全部记录下来，中间也有Sql语句部分；
        /// </summary>
        /// <param name="profiler"></param>
        private static void WriteLog(MiniProfiler profiler)
        {
            if (profiler?.Root != null)
            {
                var root = profiler.Root;
                if (root.HasChildren)
                {
                    root.Children.ForEach(chil =>
                    {
                        if (chil.CustomTimings?.Count > 0)
                        {
                            foreach (var customTiming in chil.CustomTimings)
                            {
                                var all_sql = new List<string>();
                                var err_sql = new List<string>();
                                var all_log = new List<string>();
                                int i = 1;
                                customTiming.Value?.ForEach(value =>
                                {
                                    if (value.ExecuteType != "OpenAsync")
                                        all_sql.Add(value.CommandString);
                                    if (value.Errored)
                                        err_sql.Add(value.CommandString);
                                    var log = $@"【{customTiming.Key}{i++}】{value.CommandString} Execute time :{value.DurationMilliseconds} ms,Start offset :{value.StartMilliseconds} ms,Errored :{value.Errored}";
                                    all_log.Add(log);
                                });
                                //LogUtil.GetInstance.WriteLogOld("Dapper执行SQL", Newtonsoft.Json.JsonConvert.SerializeObject(all_log), true);
                            }
                        }
                    });
                }
            }
        }

        /// <summary>
        /// 就是通过json字符串找出更新的字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="json"></param>
        /// <param name="id"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int Update<T>(string json, int id, out string msg)
        {
            msg = string.Empty;
            try
            {
                var result = this.InitConnction(out msg, WriteAndReadEnum.Write, (con) =>
                {
                    Type type = typeof(T);
                    T t = JsonUtil.ToObject<T>(json);
                    string stringSet = string.Join(",", type.GetPropertiesInJson(json).Select(p => $"{p.GetMappingName()}=@{p.Name}"));
                    var keyId = TableAtributeUtil.GetPrimaryKey<T>();
                    string sql = $"update {type.GetMappingName()} set {stringSet} where {keyId}=@{keyId};";
                    var dynamicParameters = new DynamicParameters();
                    foreach (var item in type.GetPropertiesInJson(json))
                    {
                        dynamicParameters.Add(item.GetMappingName(), item.GetValue(t));
                    }
                    dynamicParameters.Add(keyId, id);
                    var res = con.Execute(sql, dynamicParameters, null);
                    return res;
                }, false);
                return result;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return 0;
            }
        }

        /// <summary>
        /// 批量操作
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="param"></param>
        /// <param name="cURDModel"></param>
        /// <param name="isproc"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        public List<int> Execute(List<string> sqlstr, List<DynamicParameters> param, CURDModel cURDModel, bool isproc, out string exception)
        {
            exception = null;
            List<int> result = new List<int>();
            IDbTransaction tran = null;

            result = InitConnction(out exception, WriteAndReadEnum.Write, (con) =>
            {
                var index = 0;
                foreach (var sql in sqlstr)
                {
                    con.Execute(sql, param[index]);
                    var res = cURDModel.cUDType == CUDType.Add ? (int)con.ExecuteScalar(sql, param[index], tran, 120) : con.Execute(sql, param[index], tran, 120);
                    if (res < 0)
                    {
                        if (tran != null)
                            tran.Rollback();

                        result.Add(-1);
                    }
                    else
                    {
                        result.Add(res);
                    }
                    index++;
                }
                tran.Commit();
                return result;
            }, true);
            return result;
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tuples"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool ExecuteSql(string sql, List<Tuple<string, string, ExcuteType>> tuples, out string msg)
        {
            msg = null;
            var result = InitConnction(out msg, WriteAndReadEnum.Write, (con) =>
            {
                var dynamicParameters = new DynamicParameters();
                InitParam(ref sql, tuples, ref dynamicParameters);
                var res = con.Execute(sql, dynamicParameters, null, 120) >= 0;
                return res;
            }, true);
            return result;
        }

        /// <summary>
        /// 执行批量SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pairs"></param>
        /// <param name="msg"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public bool ExecuteSql(List<Tuple<string, Dictionary<string, string>>> pairs, out string msg)
        {
            msg = null;
            var result = InitConnction(out msg, WriteAndReadEnum.Write, (con) =>
            {
                var newsql = string.Empty;
                var isPass = true;
                pairs.ForEach(a =>
                {
                    var index = 0;
                    var dynamicParameters = new DynamicParameters();
                    a.Item2.ForEach((key, value) =>
                    {
                        newsql = a.Item1;
                        if (index == 0)
                        {
                            newsql += $" where {key}=@{key} ";
                        }
                        else
                        {
                            newsql += $" and {key}=@{key} ";
                        }
                        dynamicParameters.Add(key, value);
                        index++;
                        var res = con.Execute(newsql, dynamicParameters, null, 120) >= 0;
                        if (!res)
                        {
                            isPass = false;
                        }
                    });
                });

                return isPass;
            }, true);
            return result;
        }

        /// <summary>
        /// 根据主键获取信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Id"></param>
        /// <param name="exception"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool GetEntityById<T>(long Id, out string exception, out T t) where T : new()
        {
            t = new T();
            exception = null;
            var msg = string.Empty;
            t = InitConnction(out exception, WriteAndReadEnum.Read, (con) =>
            {
                var t = new T();
                Type type = typeof(T);
                var sqlParam = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.MySqlDapperQuery, default(T), out msg, out var sql, Id);
                var data = con.Query<T>(sql, new { Id = Id }, null, true, 120);
                if (data.Count() > 0)
                {
                    t = data.FirstOrDefault();
                }
                return t;
            }, false);
            return string.IsNullOrEmpty(msg) ? true : false;
        }

        /// <summary>
        /// 根据条件获取信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="tuples"></param>
        /// <param name="msg"></param>
        /// <param name="t"></param>
        public void QueryInfoBySql<T>(string sql, List<Tuple<string, string, ExcuteType>> tuples, out string exception, out T t) where T : class, new()
        {
            t = new T();
            exception = null;
            var msg = string.Empty;
            t = InitConnction(out exception, WriteAndReadEnum.Read, (con) =>
            {
                var t = new T();
                Type type = typeof(T);
                var dynamicParameters = new DynamicParameters();
                InitParam(ref sql, tuples, ref dynamicParameters);
                var data = con.Query<T>(sql, dynamicParameters, null, true, 120);
                if (data.Count() > 0)
                {
                    t = data.FirstOrDefault();
                }
                return t;
            }, false);
        }

        /// <summary>
        /// 分页查询列表信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tablename"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sqlWhere"></param>
        /// <param name="sortFile"></param>
        /// <param name="srotType"></param>
        /// <param name="count"></param>
        /// <param name="exception"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool GetList<T>(string storedprocedure, string tablename, string queryFile, int pageIndex, int pageSize, string sqlWhere,
            string sortFile, string srotType, out long count, out string exception, out List<T> list) where T : new()
        {
            list = new List<T>();
            count = 0;
            long total = 0;
            exception = string.Empty;
            list = InitConnction(out exception, WriteAndReadEnum.Read, (con) =>
            {
                var param = new DynamicParameters();
                List<T> list = new List<T>();
                param.Add("p_table_name", tablename, DbType.String, ParameterDirection.Input);
                param.Add("p_files", queryFile, DbType.String, ParameterDirection.Input);
                param.Add("p_curr_page", pageIndex, DbType.Int32, ParameterDirection.Input);
                param.Add("p_page_size", pageSize, DbType.Int32, ParameterDirection.Input);
                param.Add("p_order_string", "order by " + sortFile, DbType.String, ParameterDirection.Input);
                param.Add("p_ordertype_string", string.IsNullOrEmpty(srotType) ? "desc" : srotType, DbType.String, ParameterDirection.Input);
                param.Add("p_where_string", sqlWhere, DbType.String, ParameterDirection.Input);
                param.Add("p_total_rows", 0, DbType.Int32, ParameterDirection.Output);
                list = con.Query<T>(storedprocedure, param, null, true, 120, commandType: CommandType.StoredProcedure).ToList();
                total = param.Get<int>("p_total_rows");
                return list;
            }, false);
            count = total;
            return string.IsNullOrEmpty(exception) ? true : false;
        }

        /// <summary>
        /// 查询信息列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="tuples"></param>
        /// <param name="exception"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool GetList<T>(string sql, List<Tuple<string, string, ExcuteType>> tuples, out string exception, out List<T> list) where T : new()
        {
            list = new List<T>();
            exception = null;
            var msg = string.Empty;
            var isSuccess = true;
            list = InitConnction<List<T>>(out exception, WriteAndReadEnum.Read, (con) =>
            {
                var listc = new List<T>();
                var dynamicParameters = new DynamicParameters();
                InitParam(ref sql, tuples, ref dynamicParameters);
                listc = con.Query<T>(sql, dynamicParameters, null, true, 120, commandType: CommandType.Text).ToList();
                return listc;
            }, false);
            if (!string.IsNullOrEmpty(msg))
            {
                exception += msg;
                isSuccess = false;
            }
            return isSuccess;
        }

        /// <summary>
        /// 在一个查询中处理多个结果
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="msg"></param>
        /// <param name="action"></param>
        /*
        var sql =
        @"
        select * from Customers where CustomerId = @id
        select * from Orders where CustomerId = @id
        select * from Returns where CustomerId = @id";
        using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
        {
           var customer = multi.Read<Customer>().Single();
           var orders = multi.Read<Order>().ToList();
           var returns = multi.Read<Return>().ToList();
           ...
        }
        */
        public void QueryMultiple(string sql, object param, out string msg, Action<GridReader> action)
        {
            this.InitConnction(out msg, WriteAndReadEnum.Read, (con) =>
            {
                var multi = con.QueryMultiple(sql, param, null, 120);
                action?.Invoke(multi);
                return true;
            }, false);
        }

        /// <summary>
        /// 外部执行Query
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="msg"></param>
        /// <param name="action"></param>
        /*
         //两表关联查询
        string sql = "select * from [Student] left join [Class] on [Student].ClassId=[Class].ClassId";
        using (IDbConnection con = GetConnection())
        {
            var data = con.Query<StudentModel, ClassModel, StudentModel>(sql, (s, c) => { s.Class = c; return s; }, splitOn: "StudentId,ClassId");
            var post = data.First();
        }
         //三表联合
                string sql = "select * from [Student] left join [Class] on [Student].ClassId=[Class].ClassId left join [Teacher] on [Student].TeacherId=[Teacher].TeacherId";
                using (IDbConnection con = GetConnection())
                {
                    var data = con.Query<StudentModel, ClassModel, TeacherModel, StudentModel>(sql, (s, c, t) => { s.Class = c; s.Teacher = t; return s; }, splitOn: "StudentId,ClassId,TeacherId");
                    var post = data.First();
                }
         */
        public void Query(string sql, object param, out string msg, Action<IDbConnection> action)
        {
            this.InitConnction(out msg, WriteAndReadEnum.Read, (con) =>
            {
                action?.Invoke(con);
                return true;
            }, false);
        }

        /// <summary>
        /// DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tuples"></param>
        /// <param name="exception"></param>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public bool GetDataTable(string sql, List<Tuple<string, string, ExcuteType>> tuples, out string exception, out DataTable dataTable)
        {
            dataTable = null;
            exception = null;
            var msg = string.Empty;
            var isSuccess = true;
            dataTable = InitConnction(out exception, WriteAndReadEnum.Read, (con) =>
            {
                var dynamicParameters = new DynamicParameters();
                InitParam(ref sql, tuples, ref dynamicParameters);
                DataTable dt = new DataTable();
                dt.Load(con.ExecuteReader(sql, dynamicParameters, null, 120));
                return dt;
            }, false);
            if (!string.IsNullOrEmpty(msg))
            {
                exception += msg;
                isSuccess = false;
            }
            return isSuccess;
        }

        /// <summary>
        /// 根据SQL获取DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, object param, out string msg)
        {
            msg = string.Empty;
            return this.InitConnction(out msg, WriteAndReadEnum.Read, (con) =>
            {
                DataTable dataTable = new DataTable();
                var result = con.ExecuteReader(sql, param, null, 120);
                dataTable.Load(result);
                return dataTable;
            }, false);
        }

        /// <summary>
        /// DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="exception"></param>
        /// <param name="dataSet"></param>
        /// <returns></returns>
        public bool GetDataSet(string sql, out string exception, out DataSet dataSet)
        {
            dataSet = null;
            exception = null;
            var msg = string.Empty;
            var isSuccess = true;
            dataSet = InitConnction(out exception, WriteAndReadEnum.Read, (con) =>
            {
                DataSet ds = new DataSet();
                var data = con.ExecuteReader(sql, null, null, 120);
                ds = DataTableUtil.ConvertDataReaderToDataSet(data);
                return ds;
            }, false);
            if (!string.IsNullOrEmpty(msg))
            {
                exception += msg;
                isSuccess = false;
            }
            return isSuccess;
        }

        /// <summary>
        /// 根据SQL获取DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, object param, out string exception)
        {
            exception = null;
            var msg = string.Empty;
            return InitConnction(out exception, WriteAndReadEnum.Read, (con) =>
            {
                DataSet ds = new DataSet();
                var data = con.ExecuteReader(sql, param, null, 120);
                ds = DataTableUtil.ConvertDataReaderToDataSet(data);
                return ds;
            }, false);
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="param"></param>
        /// <param name="cURDModel"></param>
        /// <param name="isproc"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        public int Execute(string sqlstr, DynamicParameters param, CURDModel cURDModel, bool isproc, out string exception)
        {
            exception = null;
            int result = -1;
            var msg = string.Empty;
            result = InitConnction(out exception, WriteAndReadEnum.Write, (con) =>
            {
                int result = -1;
                result = cURDModel.cUDType == CUDType.Add ? Convert.ToInt32(con.ExecuteScalar(sqlstr, param, null, 120)) : con.Execute(sqlstr, param, null, 120);
                return result;
            }, false);
            return result;
        }

        /// <summary>
        /// 初始化参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tuples"></param>
        /// <param name="parameters"></param>
        private static void InitParam(ref string sql, List<Tuple<string, string, ExcuteType>> tuples, ref DynamicParameters parameters)
        {
            var index = 0;
            DynamicParameters dynamicParameters = new DynamicParameters();
            if (tuples != null && tuples.Count > 0)
            {
                string newsql = sql;
                tuples.ForEach(a =>
                {
                    if (index == 0)
                    {
                        if (a.Item3 == ExcuteType.Equal)
                        {
                            newsql += $" where {a.Item1}=@{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.In)
                        {
                            newsql += $" where {a.Item1} in @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.NoEqual)
                        {
                            newsql += $" where {a.Item1} <> @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.gte)
                        {
                            newsql += $" where {a.Item1} >= @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.lte)
                        {
                            newsql += $" where {a.Item1} <= @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.findinset)
                        {
                            newsql += $" where find_in_set({a.Item2},{a.Item1}) ";
                        }
                    }
                    else
                    {
                        if (a.Item3 == ExcuteType.Equal)
                        {
                            newsql += $" and {a.Item1}=@{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.In)
                        {
                            newsql += $" and {a.Item1} in @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.NoEqual)
                        {
                            newsql += $" and {a.Item1} <> @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.gte)
                        {
                            newsql += $" and {a.Item1} >= @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.lte)
                        {
                            newsql += $" and {a.Item1} <= @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.findinset)
                        {
                            newsql += $" and find_in_set({a.Item2},{a.Item1}) ";
                        }
                    }
                    if (a.Item3 == ExcuteType.In)
                    {
                        dynamicParameters.Add(a.Item1, a.Item2.Split(','));
                    }
                    else
                    {
                        dynamicParameters.Add(a.Item1, a.Item2);
                    }
                    index++;
                });
                sql = newsql;
                parameters = dynamicParameters;
            }
        }
    }
}
